﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Maticsoft.DBUtility;
using System.Data.SqlClient;


namespace RLZY.others
{
    class xlmoon
    {

        /// <summary>
        /// 运算
        /// </summary>
        public int opration(String startdate,String enddate,String QX)
        {
            List<String> list = new List<string>();
            list.Add(" if  exists (select name from sysobjects where name='GZ1')  drop table GZ1 ");
            list.Add(" select * into GZ1 from GZ where substring(日期,1,7)='" + startdate.Substring(0, 7) + "' ");
            list.Add(" if  exists (select name from sysobjects where name='GZ2') drop table GZ2 ");
            list.Add("   select * into GZ2 from GZ where substring(日期,1,7)='" + enddate.Substring(0, 7) + "' ");
            list.Add(" if  exists (select name from sysobjects where name='GZ3') drop table GZ3 ");
            list.Add(" select   a.[部门单位] as 部门单位本月, a.[车间班组]  as 车间班组本月, a.[姓名] as 姓名本月, a.[用工性质] as 用工性质本月, a.[技能工资] as 技能工资本月, a.[岗位工资] as 岗位工资本月, a.[绩效工资] as 绩效工资本月,  a.[三项补贴] as 三项补贴本月, a.[回贴] as 回贴本月, a.[工龄津贴] as 工龄津贴本月, a.[职称津贴] as 职称津贴本月, a.[特岗津贴] as 特岗津贴本月, a.[行车工津贴] as 行车工津贴本月 , a.[卫生纸费] as 卫生纸费本月,  a.[中晚班费] as 中晚班费本月,  a.[加班工资]  as 加班工资本月, a.[补发] as 补发本月,  a.[扣事假工资] as 扣事假工资本月,  a.[扣病假工资] as 扣病假工资本月,  a.[其它扣款] as 其它扣款本月, a.[应发总额] as 应发总额本月, a.[扣失业保险] as 扣失业保险本月, a.[扣养老金] as 扣养老金本月, a.[扣医疗保险] as 扣医疗保险本月, a.[扣公积金] as 扣公积金本月, a.[扣电视费] as 扣电视费本月, a.[扣治安费] as 扣治安费本月, a.[扣会费] as 扣会费本月, a.[扣互助金] as 扣互助金本月, a.[扣丧葬费] as 扣丧葬费本月, a.[实发金额] as 实发金额本月, a.[身份证号] as 身份证号本月,a.人数  as 人数本月 , b.[部门单位] as 部门单位上月, b.[车间班组]  as 车间班组上月, b.[姓名] as 姓名上月, b.[用工性质] as 用工性质上月, b.[技能工资] as 技能工资上月, b.[岗位工资] as 岗位工资上月, b.[绩效工资] as 绩效工资上月,  b.[三项补贴] as 三项补贴上月, b.[回贴] as 回贴上月, b.[工龄津贴] as 工龄津贴上月, b.[职称津贴] as 职称津贴上月, b.[特岗津贴] as 特岗津贴上月, b.[行车工津贴] as 行车工津贴上月 , b.[卫生纸费] as 卫生纸费上月,  b.[中晚班费] as 中晚班费上月,  b.[加班工资]  as 加班工资上月, b.[补发] as 补发上月,  b.[扣事假工资] as 扣事假工资上月,  b.[扣病假工资] as 扣病假工资上月,  b.[其它扣款] as 其它扣款上月, b.[应发总额] as 应发总额上月, b.[扣失业保险] as 扣失业保险上月, b.[扣养老金] as 扣养老金上月, b.[扣医疗保险] as 扣医疗保险上月, b.[扣公积金] as 扣公积金上月, b.[扣电视费] as 扣电视费上月, b.[扣治安费] as 扣治安费上月, b.[扣会费] as 扣会费上月, b.[扣互助金] as 扣互助金上月, b.[扣丧葬费] as 扣丧葬费上月, b.[实发金额] as 实发金额上月 ,b.[身份证号] as 身份证号上月,b.人数  as 人数上月 into GZ3 from GZ2 as a  full join  GZ1 as b on a.身份证号= b.身份证号 ");
            list.Add("  if  exists (select name from sysobjects where name='GZ4') drop table GZ4  ");
            list.Add("  select    部门单位本月 as 部门单位,  车间班组本月 as 车间班组,  姓名本月 as 姓名,  用工性质本月 as 用工性质, 人数本月 as 人数, 技能工资本月 as 技能工资,  岗位工资本月  as 岗位工资,  绩效工资本月  as 绩效工资,   三项补贴本月 as 三项补贴,  回贴本月 as 回贴,  工龄津贴本月 as 工龄津贴,  职称津贴本月 as 职称津贴,  特岗津贴本月 as 特岗津贴,  行车工津贴本月 as 行车工津贴,  卫生纸费本月 as 卫生纸费,   中晚班费本月 as 中晚班费,   加班工资本月 as 加班工资,  补发本月 as 补发,   扣事假工资本月 as  扣事假工资,   扣病假工资本月 as 扣病假工资,   其它扣款本月 as 其它扣款,  应发总额本月 as 应发总额,  扣失业保险本月 as 扣失业保险,  扣养老金本月 as 扣养老金,  扣医疗保险本月 as 扣医疗保险,  扣公积金本月 as 扣公积金,  扣电视费本月 as 扣电视费,  扣治安费本月 as 扣治安费,  扣会费本月 as 扣会费,  扣互助金本月 as 扣互助金,  扣丧葬费本月 as 扣丧葬费,  实发金额本月 as 实发金额  ,身份证号本月 as 身份证号  into GZ4  from GZ3  where  身份证号上月  is null   ");
            list.Add("  if  exists (select name from sysobjects where name='GZ5') drop table GZ5 ");
            list.Add("  select    部门单位上月 as 部门单位,  车间班组上月 as 车间班组,  姓名上月 as 姓名,  用工性质上月 as 用工性质, 人数上月 as 人数, 技能工资上月 as 技能工资,  岗位工资上月  as 岗位工资,  绩效工资上月  as 绩效工资,   三项补贴上月 as 三项补贴,  回贴上月 as 回贴,  工龄津贴上月 as 工龄津贴,  职称津贴上月 as 职称津贴,  特岗津贴上月 as 特岗津贴,  行车工津贴上月 as 行车工津贴,  卫生纸费上月 as 卫生纸费,   中晚班费上月 as 中晚班费,   加班工资上月 as 加班工资,  补发上月 as 补发,   扣事假工资上月 as  扣事假工资,   扣病假工资上月 as 扣病假工资,   其它扣款上月 as 其它扣款,  应发总额上月 as 应发总额,  扣失业保险上月 as 扣失业保险,  扣养老金上月 as 扣养老金,  扣医疗保险上月 as 扣医疗保险,  扣公积金上月 as 扣公积金,  扣电视费上月 as 扣电视费,  扣治安费上月 as 扣治安费,  扣会费上月 as 扣会费,  扣互助金上月 as 扣互助金,  扣丧葬费上月 as 扣丧葬费,  实发金额上月 as 实发金额  ,身份证号上月 as 身份证号  into GZ5 from GZ3 where  身份证号本月  is null  ");
            list.Add(" if  exists (select name from sysobjects where name='GZ6') drop table GZ6  ");
            list.Add(" select    部门单位本月 as 部门单位,  车间班组本月 as 车间班组,  姓名本月 as 姓名,  用工性质本月 as 用工性质, 人数本月 as 人数, 技能工资本月 as 技能工资,  岗位工资本月  as 岗位工资,  绩效工资本月  as 绩效工资,   三项补贴本月 as 三项补贴,  回贴本月 as 回贴,  工龄津贴本月 as 工龄津贴,  职称津贴本月 as 职称津贴,  特岗津贴本月 as 特岗津贴,  行车工津贴本月 as 行车工津贴,  卫生纸费本月 as 卫生纸费,   中晚班费本月 as 中晚班费,   加班工资本月 as 加班工资,  补发本月 as 补发,   扣事假工资本月 as  扣事假工资,   扣病假工资本月 as 扣病假工资,   其它扣款本月 as 其它扣款,  应发总额本月 as 应发总额,  扣失业保险本月 as 扣失业保险,  扣养老金本月 as 扣养老金,  扣医疗保险本月 as 扣医疗保险,  扣公积金本月 as 扣公积金,  扣电视费本月 as 扣电视费,  扣治安费本月 as 扣治安费,  扣会费本月 as 扣会费,  扣互助金本月 as 扣互助金,  扣丧葬费本月 as 扣丧葬费,  实发金额本月 as 实发金额   into GZ6  from GZ3 where  not(身份证号上月  is null  or 身份证号本月  is null) ");
            list.Add(" if  exists (select name from sysobjects where name='GZ61') drop table GZ61  ");
            list.Add(" select    用工性质, sum(人数) as 人数, sum(技能工资) as 技能工资,  sum(岗位工资)  as 岗位工资,  sum(绩效工资)  as 绩效工资,   sum(三项补贴) as 三项补贴,  sum(回贴) as 回贴,  sum(工龄津贴) as 工龄津贴,  sum(职称津贴) as 职称津贴,  sum(特岗津贴) as 特岗津贴,  sum(行车工津贴) as 行车工津贴,  sum(卫生纸费) as 卫生纸费,   sum(中晚班费) as 中晚班费,   sum(加班工资) as 加班工资,  sum(补发) as 补发,   sum(扣事假工资) as  扣事假工资,   sum(扣病假工资) as 扣病假工资,   sum(其它扣款) as 其它扣款,  sum(应发总额) as 应发总额,  Sum(扣失业保险) as 扣失业保险,  sum(扣养老金) as 扣养老金,  sum(扣医疗保险) as 扣医疗保险,  sum(扣公积金) as 扣公积金,  sum(扣电视费) as 扣电视费,  sum(扣治安费) as 扣治安费,  sum(扣会费) as 扣会费,  sum(扣互助金) as 扣互助金,  sum(扣丧葬费) as 扣丧葬费,  Sum(实发金额) as 实发金额   into GZ61  from GZ6 where  部门单位 like '%'+'" + QX + "'+'%'  group by 用工性质  ");
            list.Add(" if  exists (select name from sysobjects where name='GZ7') drop table GZ7  ");
            list.Add(" select    部门单位上月 as 部门单位,  车间班组上月 as 车间班组,  姓名上月 as 姓名,  用工性质上月 as 用工性质, 人数上月 as 人数, 技能工资上月 as 技能工资,  岗位工资上月  as 岗位工资,  绩效工资上月  as 绩效工资,   三项补贴上月 as 三项补贴,  回贴上月 as 回贴,  工龄津贴上月 as 工龄津贴,  职称津贴上月 as 职称津贴,  特岗津贴上月 as 特岗津贴,  行车工津贴上月 as 行车工津贴,  卫生纸费上月 as 卫生纸费,   中晚班费上月 as 中晚班费,   加班工资上月 as 加班工资,  补发上月 as 补发,   扣事假工资上月 as  扣事假工资,   扣病假工资上月 as 扣病假工资,   其它扣款上月 as 其它扣款,  应发总额上月 as 应发总额,  扣失业保险上月 as 扣失业保险,  扣养老金上月 as 扣养老金,  扣医疗保险上月 as 扣医疗保险,  扣公积金上月 as 扣公积金,  扣电视费上月 as 扣电视费,  扣治安费上月 as 扣治安费,  扣会费上月 as 扣会费,  扣互助金上月 as 扣互助金,  扣丧葬费上月 as 扣丧葬费,  实发金额上月 as 实发金额   into GZ7  from GZ3 where  not(身份证号上月  is null  or 身份证号本月  is null) ");
            list.Add(" if  exists (select name from sysobjects where name='GZ71') drop table GZ71  ");
            list.Add(" select    用工性质, sum(人数) as 人数, sum(技能工资) as 技能工资,  sum(岗位工资)  as 岗位工资,  sum(绩效工资)  as 绩效工资,   sum(三项补贴) as 三项补贴,  sum(回贴) as 回贴,  sum(工龄津贴) as 工龄津贴,  sum(职称津贴) as 职称津贴,  sum(特岗津贴) as 特岗津贴,  sum(行车工津贴) as 行车工津贴,  sum(卫生纸费) as 卫生纸费,   sum(中晚班费) as 中晚班费,   sum(加班工资) as 加班工资,  sum(补发) as 补发,   sum(扣事假工资) as  扣事假工资,   sum(扣病假工资) as 扣病假工资,   sum(其它扣款) as 其它扣款,  sum(应发总额) as 应发总额,  Sum(扣失业保险) as 扣失业保险,  sum(扣养老金) as 扣养老金,  sum(扣医疗保险) as 扣医疗保险,  sum(扣公积金) as 扣公积金,  sum(扣电视费) as 扣电视费,  sum(扣治安费) as 扣治安费,  sum(扣会费) as 扣会费,  sum(扣互助金) as 扣互助金,  sum(扣丧葬费) as 扣丧葬费,  Sum(实发金额) as 实发金额   into GZ71  from GZ7 where  部门单位 like '%'+'" + QX + "'+'%'  group by 用工性质 ");
            list.Add(" update GZ71 set 用工性质=用工性质+'---上月' ");
            list.Add(" insert into GZ61  select   用工性质,  人数,  技能工资,   岗位工资,   绩效工资,    三项补贴,   回贴,   工龄津贴,   职称津贴,   特岗津贴,   行车工津贴,   卫生纸费,    中晚班费,    加班工资,   补发,     扣事假工资,    扣病假工资,    其它扣款,   应发总额,   扣失业保险,   扣养老金,   扣医疗保险,   扣公积金,   扣电视费,   扣治安费,   扣会费,   扣互助金,   扣丧葬费,  实发金额     from GZ71 ");
            list.Add(" if  exists (select name from sysobjects where name='GZ8') drop table GZ8 ");
            list.Add(" select    姓名本月 as 姓名,   技能工资本月 as 技能工资,  岗位工资本月  as 岗位工资,   工龄津贴本月 as 工龄津贴,  职称津贴本月 as 职称津贴,  特岗津贴本月 as 特岗津贴,  行车工津贴本月 as 行车工津贴,    扣失业保险本月 as 扣失业保险,  扣养老金本月 as 扣养老金,  扣医疗保险本月 as 扣医疗保险,  扣公积金本月 as 扣公积金,  扣电视费本月 as 扣电视费,  扣治安费本月 as 扣治安费   into GZ8  from GZ3 where  not(身份证号上月  is null  or 身份证号本月  is null)   and (  技能工资本月 <> 技能工资上月  or   岗位工资本月   <> 岗位工资上月  or    工龄津贴本月  <> 工龄津贴上月  or   职称津贴本月 <> 职称津贴上月  or   特岗津贴本月  <> 特岗津贴上月  or   行车工津贴本月  <> 行车工津贴上月  or     扣失业保险本月  <> 扣失业保险上月  or   扣养老金本月  <> 扣养老金上月  or   扣医疗保险本月 <> 扣医疗保险上月  or   扣公积金本月  <> 扣公积金上月  or   扣电视费本月 <> 扣电视费上月  or   扣治安费本月  <> 扣治安费上月     )  ");
            list.Add(" update GZ8 set 姓名=姓名+'-B'     ");
            list.Add(" if  exists (select name from sysobjects where name='GZ9') drop table GZ9  ");
            list.Add(" select    姓名上月 as 姓名,   技能工资上月 as 技能工资,  岗位工资上月  as 岗位工资,   工龄津贴上月 as 工龄津贴,  职称津贴上月 as 职称津贴,  特岗津贴上月 as 特岗津贴,  行车工津贴上月 as 行车工津贴,    扣失业保险上月 as 扣失业保险,  扣养老金上月 as 扣养老金,  扣医疗保险上月 as 扣医疗保险,  扣公积金上月 as 扣公积金,  扣电视费上月 as 扣电视费,  扣治安费上月 as 扣治安费   into GZ9  from GZ3 where  not(身份证号上月  is null  or 身份证号本月  is null)   and (  技能工资本月 <> 技能工资上月  or   岗位工资本月   <> 岗位工资上月  or    工龄津贴本月  <> 工龄津贴上月  or   职称津贴本月 <> 职称津贴上月  or   特岗津贴本月  <> 特岗津贴上月  or   行车工津贴本月  <> 行车工津贴上月  or     扣失业保险本月  <> 扣失业保险上月  or   扣养老金本月  <> 扣养老金上月  or   扣医疗保险本月 <> 扣医疗保险上月  or   扣公积金本月  <> 扣公积金上月  or   扣电视费本月 <> 扣电视费上月  or   扣治安费本月  <> 扣治安费上月     ) ");
            list.Add(" update GZ9 set 姓名=姓名+'-S'     ");
            list.Add(" insert into GZ8  select   姓名,  技能工资,   岗位工资,   工龄津贴,   职称津贴,   特岗津贴,   行车工津贴,     扣失业保险,   扣养老金,   扣医疗保险,   扣公积金,   扣电视费,   扣治安费   from GZ9 ");
            return DbHelperSQL.ExecuteSqlTran(list);
        }
        /// <summary>
        /// 单位技能工资汇总
        /// </summary>
        public DataSet unitJINZHZ(string 部门单位, String 日期)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select   [用工性质],sum(人数) as 人数,sum(技能工资) as 技能工资   from GZ   ");
            strSql.Append(" where 1=1 and (部门单位 like '%'+@strWhere+'%' or @strWhere='') ");
            strSql.Append(" and substring(日期,1,7)=@日期  ");
            strSql.Append("   and   技能工资<>0  group by 用工性质 with CUBE  ");
            SqlParameter[] param = new SqlParameter[] 
            { 
                
                new SqlParameter("@strWhere",部门单位),
                new SqlParameter("@日期",日期.Substring(0,7)),
                
            };
            return DbHelperSQL.Query(strSql.ToString(), param);
        }
        /// <summary>
        /// 本部技能工资汇总
        /// </summary>
        public DataSet BBJINZHZ(String 日期)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select   [用工性质],sum(人数) as 人数,  sum(技能工资) as 技能工资   from GZ   ");
            strSql.Append("   where     substring(日期,1,7)=@日期 ");
            strSql.Append("  and (部门单位='特钢机关' or 部门单位='型钢厂'  or  部门单位='冷带厂' or  部门单位='新安特' or  部门单位='工贸')  and  技能工资<>0");
            strSql.Append("    group by 用工性质 with CUBE   ");
            SqlParameter[] param = new SqlParameter[] 
            { 
                
                //new SqlParameter("@strWhere",部门单位),
                new SqlParameter("@日期",日期.Substring(0,7)),
                
            };
            return DbHelperSQL.Query(strSql.ToString(), param);
        }
        /// <summary>
        /// 单位岗位工资汇总
        /// </summary>
        public DataSet unitGWGZHZ(string 部门单位, String 日期)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select   [用工性质],sum(人数) as 人数,sum(岗位工资) as 岗位工资    from GZ   ");
            strSql.Append(" where 1=1 and (部门单位 like '%'+@strWhere+'%' or @strWhere='') ");
            strSql.Append(" and substring(日期,1,7)=@日期  ");
            strSql.Append("   and  岗位工资 <>0  group by 用工性质 with CUBE  ");
            SqlParameter[] param = new SqlParameter[] 
            { 
                
                new SqlParameter("@strWhere",部门单位),
                new SqlParameter("@日期",日期.Substring(0,7)),
                
            };
            return DbHelperSQL.Query(strSql.ToString(), param);
        }
        /// <summary>
        /// 本部岗位工资汇总
        /// </summary>
        public DataSet BBGWGZHZ(String 日期)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select   [用工性质],sum(人数) as 人数,  sum(岗位工资) as 岗位工资    from GZ   ");
            strSql.Append("   where     substring(日期,1,7)=@日期 ");
            strSql.Append("  and (部门单位='特钢机关' or 部门单位='型钢厂'  or  部门单位='冷带厂' or  部门单位='新安特' or  部门单位='工贸')  and  技能工资<>0");
            strSql.Append(" and  岗位工资 <>0  group by 用工性质 with CUBE  ");
            SqlParameter[] param = new SqlParameter[] 
            { 
                
                //new SqlParameter("@strWhere",部门单位),
                new SqlParameter("@日期",日期.Substring(0,7)),
                
            };
            return DbHelperSQL.Query(strSql.ToString(), param);
        }
        /// <summary>
        /// 单位养老金汇总
        /// </summary>
        public DataSet unitYLJHV(string 部门单位, String 日期)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select   [用工性质],sum(人数) as 人数,sum(扣养老金) as 扣养老金    from GZ   ");
            strSql.Append(" where 1=1 and (部门单位 like '%'+@strWhere+'%' or @strWhere='') ");
            strSql.Append(" and substring(日期,1,7)=@日期  ");
            strSql.Append("  and  扣养老金 <>0  group by 用工性质 with CUBE  ");
            SqlParameter[] param = new SqlParameter[] 
            { 
                
                new SqlParameter("@strWhere",部门单位),
                new SqlParameter("@日期",日期.Substring(0,7)),
                
            };
            return DbHelperSQL.Query(strSql.ToString(), param);
        }
        /// <summary>
        /// 本部养老金汇总
        /// </summary>
        public DataSet BBYLJHZ(String 日期)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  [用工性质],sum(人数) as 人数,sum(扣养老金) as 扣养老金    from GZ   ");
            strSql.Append("   where     substring(日期,1,7)=@日期 ");
            strSql.Append("  and (部门单位='特钢机关' or 部门单位='型钢厂'  or  部门单位='冷带厂' or  部门单位='新安特' or  部门单位='工贸')  and  技能工资<>0");
            strSql.Append(" and  扣养老金 <>0  group by 用工性质 with CUBE  ");
            SqlParameter[] param = new SqlParameter[] 
            { 
                
                //new SqlParameter("@strWhere",部门单位),
                new SqlParameter("@日期",日期.Substring(0,7)),
                
            };
            return DbHelperSQL.Query(strSql.ToString(), param);
        }
        /// <summary>
        /// 单位失业金汇总
        /// </summary>
        public DataSet unitSYJHZ(string 部门单位, String 日期)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select   [用工性质],sum(人数) as 人数,sum(扣失业保险) as 扣失业保险   from GZ   ");
            strSql.Append(" where 1=1 and (部门单位 like '%'+@strWhere+'%' or @strWhere='') ");
            strSql.Append(" and substring(日期,1,7)=@日期  ");
            strSql.Append("   and  扣失业保险 <>0  group by 用工性质 with CUBE  ");
            SqlParameter[] param = new SqlParameter[] 
            { 
                
                new SqlParameter("@strWhere",部门单位),
                new SqlParameter("@日期",日期.Substring(0,7)),
                
            };
            return DbHelperSQL.Query(strSql.ToString(), param);
        }

        /// <summary>
        /// 本部失业金汇总
        /// </summary>
        public DataSet BBSYJHZ(String 日期)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select   [用工性质],sum(人数) as 人数,  sum(扣失业保险) as 扣失业保险     from GZ   ");
            strSql.Append("   where     substring(日期,1,7)=@日期 ");
            strSql.Append("  and (部门单位='特钢机关' or 部门单位='型钢厂'  or  部门单位='冷带厂' or  部门单位='新安特' or  部门单位='工贸')  and  技能工资<>0");
            strSql.Append("  and  扣失业保险 <>0  group by 用工性质 with CUBE  ");
            SqlParameter[] param = new SqlParameter[] 
            { 
                
                //new SqlParameter("@strWhere",部门单位),
                new SqlParameter("@日期",日期.Substring(0,7)),
                
            };
            return DbHelperSQL.Query(strSql.ToString(), param);
        }
        /// <summary>
        /// 单位医疗金汇总
        /// </summary>
        public DataSet unitYLJHZ(string 部门单位, String 日期)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select   [用工性质],sum(人数) as 人数,sum(扣医疗保险) as 扣医疗保险   from GZ   ");
            strSql.Append(" where 1=1 and (部门单位 like '%'+@strWhere+'%' or @strWhere='') ");
            strSql.Append(" and substring(日期,1,7)=@日期  ");
            strSql.Append("   and  扣医疗保险 <>0  group by 用工性质 with CUBE  ");
            SqlParameter[] param = new SqlParameter[] 
            { 
                
                new SqlParameter("@strWhere",部门单位),
                new SqlParameter("@日期",日期.Substring(0,7)),
                
            };
            return DbHelperSQL.Query(strSql.ToString(), param);
        }
        /// <summary>
        /// 本部医疗金汇总
        /// </summary>
        public DataSet BBYiLiaoJHZ(String 日期)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select   [用工性质],sum(人数) as 人数,  sum(扣医疗保险) as 扣医疗保险     from GZ   ");
            strSql.Append("   where     substring(日期,1,7)=@日期 ");
            strSql.Append("  and (部门单位='特钢机关' or 部门单位='型钢厂'  or  部门单位='冷带厂' or  部门单位='新安特' or  部门单位='工贸')  and  技能工资<>0");
            strSql.Append("  and  扣医疗保险<>0  group by 用工性质 with CUBE  ");
            SqlParameter[] param = new SqlParameter[] 
            { 
                
                //new SqlParameter("@strWhere",部门单位),
                new SqlParameter("@日期",日期.Substring(0,7)),
                
            };
            return DbHelperSQL.Query(strSql.ToString(), param);
        }
        /// <summary>
        /// 单位公积金汇总
        /// </summary>
        public DataSet unitGJJHZ(string 部门单位, String 日期)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select   [用工性质],sum(人数) as 人数,sum(扣公积金) as 扣公积金   from GZ   ");
            strSql.Append(" where 1=1 and (部门单位 like '%'+@strWhere+'%' or @strWhere='') ");
            strSql.Append(" and substring(日期,1,7)=@日期  ");
            strSql.Append("  and  扣公积金<>0  group by 用工性质 with CUBE   ");
            SqlParameter[] param = new SqlParameter[] 
            { 
                
                new SqlParameter("@strWhere",部门单位),
                new SqlParameter("@日期",日期.Substring(0,7)),
                
            };
            return DbHelperSQL.Query(strSql.ToString(), param);
        }
        /// <summary>
        /// 本部公积金汇总
        /// </summary>
        public DataSet BBGJJHZ(String 日期)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select   [用工性质],sum(人数) as 人数,  sum(扣公积金) as 扣公积金     from GZ   ");
            strSql.Append("   where     substring(日期,1,7)=@日期 ");
            strSql.Append("  and (部门单位='特钢机关' or 部门单位='型钢厂'  or  部门单位='冷带厂' or  部门单位='新安特' or  部门单位='工贸')  and  技能工资<>0");
            strSql.Append("  and  扣公积金<>0  group by 用工性质 with CUBE ");
            SqlParameter[] param = new SqlParameter[] 
            { 
                
                //new SqlParameter("@strWhere",部门单位),
                new SqlParameter("@日期",日期.Substring(0,7)),
                
            };
            return DbHelperSQL.Query(strSql.ToString(), param);
        }
        /// <summary>
        /// 减人清单
        /// </summary>
        public DataSet ReducePeopleList(string 部门单位)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  * from GZ5    ");
            strSql.Append(" where 1=1 and (部门单位 like '%'+@strWhere+'%' or @strWhere='') ");
            SqlParameter[] param = new SqlParameter[] 
            { 
                new SqlParameter("@strWhere",部门单位),
            };
            return DbHelperSQL.Query(strSql.ToString(), param);
        }
        /// <summary>
        /// 增人清单
        /// </summary>
        public DataSet AddPeopleList(string 部门单位)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  * from GZ4 ");
            strSql.Append(" where 1=1 and (部门单位 like '%'+@strWhere+'%' or @strWhere='') ");
            SqlParameter[] param = new SqlParameter[] 
            { 
                new SqlParameter("@strWhere",部门单位),
            };
            return DbHelperSQL.Query(strSql.ToString(), param);
        }
        /// <summary>
        /// 减人汇总
        /// </summary>
        public DataSet ReducePeopleSum(string 部门单位)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select    用工性质, sum(人数) as 人数, sum(技能工资) as 技能工资,  sum(岗位工资)  as 岗位工资,  sum(绩效工资)  as 绩效工资,   sum(三项补贴) as 三项补贴,  sum(回贴) as 回贴,  sum(工龄津贴) as 工龄津贴,  sum(职称津贴) as 职称津贴,  sum(特岗津贴) as 特岗津贴,  sum(行车工津贴) as 行车工津贴,  sum(卫生纸费) as 卫生纸费,   sum(中晚班费) as 中晚班费,   sum(加班工资) as 加班工资,  sum(补发) as 补发,   sum(扣事假工资) as  扣事假工资,   sum(扣病假工资) as 扣病假工资,   sum(其它扣款) as 其它扣款,  sum(应发总额) as 应发总额,  Sum(扣失业保险) as 扣失业保险,  sum(扣养老金) as 扣养老金,  sum(扣医疗保险) as 扣医疗保险,  sum(扣公积金) as 扣公积金,  sum(扣电视费) as 扣电视费,  sum(扣治安费) as 扣治安费,  sum(扣会费) as 扣会费,  sum(扣互助金) as 扣互助金,  sum(扣丧葬费) as 扣丧葬费,  Sum(实发金额) as 实发金额     from GZ5   ");

            strSql.Append(" where 1=1 and (部门单位 like '%'+@strWhere+'%' or @strWhere='') ");
            strSql.Append(" group by 用工性质 with CUBE    ");

            SqlParameter[] param = new SqlParameter[] 
            { 
                new SqlParameter("@strWhere",部门单位),
            };
            return DbHelperSQL.Query(strSql.ToString(), param);
        }
        /// <summary>
        /// 增人汇总
        /// </summary>
        public DataSet AddPeopleSum(string 部门单位)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select    用工性质, sum(人数) as 人数, sum(技能工资) as 技能工资,  sum(岗位工资)  as 岗位工资,  sum(绩效工资)  as 绩效工资,   sum(三项补贴) as 三项补贴,  sum(回贴) as 回贴,  sum(工龄津贴) as 工龄津贴,  sum(职称津贴) as 职称津贴,  sum(特岗津贴) as 特岗津贴,  sum(行车工津贴) as 行车工津贴,  sum(卫生纸费) as 卫生纸费,   sum(中晚班费) as 中晚班费,   sum(加班工资) as 加班工资,  sum(补发) as 补发,   sum(扣事假工资) as  扣事假工资,   sum(扣病假工资) as 扣病假工资,   sum(其它扣款) as 其它扣款,  sum(应发总额) as 应发总额,  Sum(扣失业保险) as 扣失业保险,  sum(扣养老金) as 扣养老金,  sum(扣医疗保险) as 扣医疗保险,  sum(扣公积金) as 扣公积金,  sum(扣电视费) as 扣电视费,  sum(扣治安费) as 扣治安费,  sum(扣会费) as 扣会费,  sum(扣互助金) as 扣互助金,  sum(扣丧葬费) as 扣丧葬费,  Sum(实发金额) as 实发金额     from GZ4  ");
            strSql.Append("  where 1=1 and (部门单位 like '%'+@strWhere+'%' or @strWhere='') ");
            strSql.Append(" group by 用工性质 with CUBE    ");

            SqlParameter[] param = new SqlParameter[] 
            { 
                new SqlParameter("@strWhere",部门单位),
            };
            return DbHelperSQL.Query(strSql.ToString(), param);
        }
        /// <summary>
        /// 不变人员项目变化清单
        /// </summary>
        public DataSet NotChange()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select  * from GZ8  order by  姓名    ");

            
            return DbHelperSQL.Query(strSql.ToString(), null);
        }
        /// <summary>
        /// 不变人员相邻二月汇总
        /// </summary>
        public DataSet NotChangeSum()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select  * from GZ61  order by  用工性质 ");


            return DbHelperSQL.Query(strSql.ToString(), null);
        }
        /// <summary>
        /// 本部三种用工统计1
        /// </summary>
        public int Creat3YGTJ( String enddate)
        {
            List<String> list = new List<string>();
            list.Add("if  exists (select name from sysobjects where name='GZ8')  drop table GZ8  ");
            list.Add("select  用工性质, sum(人数) as 人数, sum(中班) as 中班,   sum(晚班) as 晚班, sum(大晚班) as 大晚班, sum(中晚班费) as 新标中晚班费 into GZ8 from GZ where substring(日期,1,7)='" + enddate.Substring(0, 7) + "' and  (部门单位='特钢机关' or 部门单位='型钢厂'  or  部门单位='冷带厂' or  部门单位='新安特' or  部门单位='工贸')  and  not(用工性质='家' or 用工性质='大')      and (中班>0 or 晚班>0 or 大晚班>0) group by 用工性质  ");
            
            return DbHelperSQL.ExecuteSqlTran(list);
        }
        /// <summary>
        /// 本部三种用工统计2
        /// </summary>
        public DataSet Select3YGTJ()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select   用工性质,人数,  中班,中班*6 as 新标中班费,中班*4 as 旧标中班费,  (中班*6- 中班*4) as  中班费增加额,  晚班,晚班*9 as 新标晚班费,晚班*6 as 旧标晚班费,  (晚班*9- 晚班*6) as  晚班费增加额 , 大晚班,大晚班*12 as 新标大晚班费,大晚班*9 as 旧标大晚班费,  (大晚班*12- 大晚班*9) as  大晚班费增加额 ,  新标中晚班费,(中班*4+晚班*6+大晚班*9)  as  旧标中晚班费, 新标中晚班费-(中班*4+晚班*6+大晚班*9) as 中晚班总增加额  from GZ8  ");


            return DbHelperSQL.Query(strSql.ToString(), null);
        }
        /// <summary>
        /// 本部三种用工统计3
        /// </summary>
        public int Drop3YGTJ()
        {
            List<String> list = new List<string>();
            list.Add(" if  exists (select name from sysobjects where name='GZ8') drop table GZ8  ");

            return DbHelperSQL.ExecuteSqlTran(list);
        }
        /// <summary>
        /// 离岗员工查询
        /// </summary>
        public DataSet GetListLG(string 姓名, String 身份证号)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select   *  from GZ_LG    ");
            strSql.Append(" where 1=1 and (姓名 like '%'+@strWhere+'%' or @strWhere='') and (身份证号 like '%'+@strWhere1+'%' or @strWhere1='') ");
            strSql.Append("  order by 日期 desc   ");
            
            SqlParameter[] param = new SqlParameter[] 
            { 
                
                new SqlParameter("@strWhere",姓名),
                new SqlParameter("@strWhere1",身份证号),
                
            };
            return DbHelperSQL.Query(strSql.ToString(), param);
        }
        /// <summary>
        /// 删除temp
        /// </summary>
        public int droptemp()
        {
            List<String> list = new List<string>();
            list.Add(" if  exists (select name from sysobjects where name='tmp')  drop table tmp  ");
            return DbHelperSQL.ExecuteSqlTran(list);
        }

        /// <summary>
        /// 内退员工显示
        /// </summary>
        public int ShowNT(String lastdate,String date)
        {
            List<String> list = new List<string>();
            list.Add(" if  exists (select name from sysobjects where name='tmp')  drop table tmp ");
            list.Add(" select [部门单位], [车间班组], [姓名], [性别],  [岗位名称], [参加工作时间], [身份证号], [用工性质],  [出生日期], [党政职别], [职称], [干工标识], [籍贯_出生地], [毕业时间文化程度], 日期 into tmp from GZ   where 日期='" + lastdate + "' ");
            list.Add(" update  tmp   set  日期=  '" + date + "'   ");
            //list.Add(" ");

            return DbHelperSQL.ExecuteSqlTran(list);
        }
        /// <summary>
        /// 获得内退员工表
        /// </summary>
        public DataSet GetListNT()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select  * from tmp   where    (datediff(yy,convert(datetime,出生日期),convert(datetime,日期))>=45  and 性别='女')  or (datediff(yy,convert(datetime,出生日期),convert(datetime,日期))>=55 and 性别='男' )   order by 部门单位 asc ,车间班组 asc,  出生日期 asc  ");
            

            
            return DbHelperSQL.Query(strSql.ToString(), null);
        }
    }
}
